/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dal;

import connection.ConnectionDB;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.Hotels;

/**
 *
 * @author Administrator
 */
public class HotelsDAL {

    private ConnectionDB conn = new ConnectionDB();
    private PlacesDAL placesDAL = new PlacesDAL();
    private CallableStatement cs;

    public boolean newHotels(Hotels hotel) {
        try {
            cs = conn.createConnection().prepareCall("{call sp_NewHotel(?,?,?,?,?)}");
            cs.setInt("placeID", hotel.getPlaceID());
            cs.setString("name", hotel.getHotelName());
            cs.setInt("rate", hotel.getRate());
            cs.setFloat("price", hotel.getPrice());
            cs.setString("images", hotel.getImages());
            if (cs.executeUpdate() == 1) {
                return true;
            }
            return false;
        } catch (SQLException ex) {
            Logger.getLogger(UserDAL.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    public boolean updateHotels(Hotels hotel) {
        try {
            cs = conn.createConnection().prepareCall("{call sp_UpdateHotel(?,?,?,?,?)}");
            cs.setInt("hotelID", hotel.getHotelID());
            cs.setInt("placeID", hotel.getPlaceID());
            cs.setString("name", hotel.getHotelName());
            cs.setInt("rate", hotel.getRate());
            cs.setFloat("price", hotel.getPrice());
            if (cs.executeUpdate() == 1) {
                return true;
            }
            return false;
        } catch (SQLException ex) {
            Logger.getLogger(UserDAL.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    public List<Hotels> listHotelByPlaceID(int placeID) {
        try {
            List<Hotels> list = new ArrayList<Hotels>();
            Hotels hotel;
            cs = conn.createConnection().prepareCall("{call sp_ListHotelByPlaceID(?)}");
            cs.setInt("placeID", placeID);
            ResultSet rs = cs.executeQuery();

            while (rs.next()) {
                hotel = new Hotels(rs.getInt("HotelID"), rs.getInt("PlaceID"), rs.getString("HotelName"), rs.getInt("HotelRate"), rs.getFloat("HotelPrice"));
                list.add(hotel);
            }
            return list;
        } catch (SQLException ex) {
            Logger.getLogger(HotelsDAL.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public List<Hotels> listAllHotel() {
        try {
            List<Hotels> listHotel = new ArrayList<Hotels>();
            Hotels hotel;
            cs = conn.createConnection().prepareCall("{call sp_ListHotel}");
            ResultSet rs = cs.executeQuery();

            while (rs.next()) {
                hotel = new Hotels(rs.getInt("HotelID"), rs.getInt("PlaceID"), rs.getString("HotelName"), rs.getInt("HotelRate"), rs.getFloat("HotelPrice"), placesDAL.listAllPlace(), rs.getString("Images"));
                listHotel.add(hotel);
            }
            return listHotel;
        } catch (SQLException ex) {
            Logger.getLogger(HotelsDAL.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public int getRowInHotel() {
        try {
            int rowInTable = 0;
            cs = conn.createConnection().prepareCall("{call sp_GetRowInHotel}");
            ResultSet rs = cs.executeQuery();

            if (rs.next()) {
                rowInTable = rs.getInt("rows");
                return rowInTable;
            }
            return 0;
        } catch (SQLException ex) {
            Logger.getLogger(HotelsDAL.class.getName()).log(Level.SEVERE, null, ex);
            return 0;
        }
    }

    public List<Hotels> listHotelByPage(int pageID) {
        try {
            List<Hotels> listHotel = new ArrayList<Hotels>();
            Hotels hotel;
            cs = conn.createConnection().prepareCall("{call sp_ListHotelByPage(?)}");
            cs.setInt("indexpage", pageID);
            ResultSet rs = cs.executeQuery();

            while (rs.next()) {
                hotel = new Hotels(rs.getInt("HotelID"), rs.getInt("PlaceID"), rs.getString("HotelName"), rs.getInt("HotelRate"), rs.getFloat("HotelPrice"), placesDAL.listAllPlace(), rs.getString("Images"));
                listHotel.add(hotel);
            }
            return listHotel;
        } catch (SQLException ex) {
            Logger.getLogger(HotelsDAL.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public List<Hotels> searchHotelByPlace(String placeName) {
        try {
            List<Hotels> list = new ArrayList<Hotels>();
            Hotels hotel;
            cs = conn.createConnection().prepareCall("{call sp_SearchHotelByPlace(?)}");
            cs.setString("placename", placeName);
            ResultSet rs = cs.executeQuery();

            while (rs.next()) {
                hotel = new Hotels(rs.getInt("HotelID"), rs.getInt("PlaceID"), rs.getString("HotelName"), rs.getInt("HotelRate"), rs.getFloat("HotelPrice"), placesDAL.listAllPlace(), rs.getString("Images"));
                list.add(hotel);
            }
            return list;
        } catch (SQLException ex) {
            Logger.getLogger(FlightDAL.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public List<Hotels> searchHotelByName(String hotelName) {
        try {
            List<Hotels> list = new ArrayList<Hotels>();
            Hotels hotel;
            cs = conn.createConnection().prepareCall("{call sp_SearchHotelByName(?)}");
            cs.setString("hotelname", hotelName);
            ResultSet rs = cs.executeQuery();

            while (rs.next()) {
                hotel = new Hotels(rs.getInt("HotelID"), rs.getInt("PlaceID"), rs.getString("HotelName"), rs.getInt("HotelRate"), rs.getFloat("HotelPrice"), placesDAL.listAllPlace(), rs.getString("Images"));
                list.add(hotel);
            }
            return list;
        } catch (SQLException ex) {
            Logger.getLogger(FlightDAL.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public List<Hotels> searchHotelByPrice(float priceFrom, float priceTo) {
        try {
            List<Hotels> list = new ArrayList<Hotels>();
            Hotels hotel;
            cs = conn.createConnection().prepareCall("{call sp_SearchHotelByPrice(?,?)}");
            cs.setFloat("pricefrom", priceFrom);
            cs.setFloat("priceto", priceTo);
            ResultSet rs = cs.executeQuery();

            while (rs.next()) {
                hotel = new Hotels(rs.getInt("HotelID"), rs.getInt("PlaceID"), rs.getString("HotelName"), rs.getInt("HotelRate"), rs.getFloat("HotelPrice"), placesDAL.listAllPlace(), rs.getString("Images"));
                list.add(hotel);
            }
            return list;
        } catch (SQLException ex) {
            Logger.getLogger(FlightDAL.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public List<Hotels> advanceSearchHotel(String hotelName , String placeName ,float priceFrom, float priceTo) {
        try {
            List<Hotels> list = new ArrayList<Hotels>();
            Hotels hotel;
            cs = conn.createConnection().prepareCall("{call sp_advanceSearchHotel(?,?,?,?)}");
            cs.setString("keyword",hotelName);
            cs.setString("placename",placeName);
            cs.setFloat("pricefrom", priceFrom);
            cs.setFloat("priceto", priceTo);
            ResultSet rs = cs.executeQuery();

            while (rs.next()) {
                hotel = new Hotels(rs.getInt("HotelID"), rs.getInt("PlaceID"), rs.getString("HotelName"), rs.getInt("HotelRate"), rs.getFloat("HotelPrice"), placesDAL.listAllPlace(), rs.getString("Images"));
                list.add(hotel);
            }
            return list;
        } catch (SQLException ex) {
            Logger.getLogger(FlightDAL.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }
}
